package controller.expertInfo.adminEdit;

import StaticFinal.CrudType;
import controller.common.BaseController;
import controller.common.StaticConstant;
import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import services.dictionary.CertificateTypeServices;
import services.dictionary.LeaveReasonServices;
import services.dictionary.MajorTypeServices;
import services.expertInfo.*;
import services.specific.AdminCheckExpertServices;
import util.DateTimeUtil;
import util.IdCardUtil;
import util.MapUtil;
import util.ValidatorUtil;
import util.io.PoiExcelReadUtil;
import wt.entity.dictionary.CertificateType;
import wt.entity.dictionary.LeaveReason;
import wt.entity.expertInfo.*;
import wt.staticData.ShortData;
import wt.staticData.TableName;

import java.util.*;

;
;

/**
 * 功能:
 * 作者: 洪世成
 * 日期: 2017/9/9
 * 版权所有: admin
 */
@Controller

@RequestMapping("expertInfo/adminEdit/uploadExpertInfo")
public class UploadExpertInfoController extends BaseController {

    @Autowired
    ExpertBaseServices expertBaseServices;
    @Autowired
    ExpertCompanyServices expertCompanyServices;
    @Autowired
    ExpertCertificateServices expertCertificateServices;
    @Autowired
    ExpertWorkServices expertWorkServices;
    @Autowired
    ExpertEducationServices expertEducationServices;
    @Autowired
    ExpertReviewServices expertReviewServices;
    @Autowired
    ExpertTrainServices expertTrainServices;
    @Autowired
    ExpertMajorServices expertMajorServices;
    @Autowired
    LeaderCheckServices uploadExpertInfoServices;
    @Autowired
    AdminCheckExpertServices uploadExpertInfoExpertServices;
    @Autowired
    CertificateTypeServices certificateTypeServices;
    @Autowired
    LeaveReasonServices leaveReasonServices;
    @Autowired
    MajorTypeServices majorTypeServices;

    @RequiresPermissions("expertInfo:adminEdit:edit")
    @RequestMapping("index")
    public String index(Map map) throws Exception {
        return "expertInfo/adminEdit/uploadExpertInfo";
    }

    @RequiresPermissions("expertInfo:adminEdit:edit")
    @RequestMapping("insert")
    public String insert(String tableName, MultipartFile file, Map map) throws Exception {

        map.put("tableName", tableName);
        map.put("isSuccess", false);


        if (file.getSize() == 0 || StringUtils.isEmpty(tableName)) {
            map.put("errorMessage", "信息类型和文件都不能为空！");
            return index(map);

        }


        List<String[]> list = null;
        List<String[]> errorList = new ArrayList<String[]>();
        try {
            list = PoiExcelReadUtil.readExcel(file.getInputStream(), 2, 0, 13, 0);
        } catch (Exception e) {
            e.printStackTrace();
            map.put("errorMessage", "文件读取异常");
            return index(map);
        }


        if (list == null || list.isEmpty()) {
            map.put("errorMessage", "未能成功读取一条有效数据");
            return index(map);
        } else {
            map.put("rowCount", list.size());
        }


        if (TableName.EMS_EXPERT_BASE.equals(tableName)) {

            List<ExpertBase> dataList = new ArrayList<ExpertBase>();
            if (analysisExpertBase(list, errorList, dataList)) {
                expertBaseServices.adminInsertSelective(dataList);
                map.put("isSuccess", true);
            } else {
                map.put("errorData", errorList);
                map.put("columnTitle", new String[]{"姓名", "身份证", "性别", "手机号码", "邮件", "银行卡号", "开户行", "是否退休", "是否教师", "是否外省专家", "最高学历", "职称", "是否启用"});
            }

        } else if (TableName.EMS_EXPERT_CERTIFICATE.equals(tableName)) {

            List<ExpertCertificate> dataList = new ArrayList<ExpertCertificate>();

            if (analysisExpertCertificate(list, errorList, dataList)) {
                expertCertificateServices.adminInsertSelective(dataList);
                map.put("isSuccess", true);
            } else {
                map.put("errorData", errorList);
                map.put("columnTitle", new String[]{"姓名", "身份证号", "证书类型", "证书名称", "证书编号", "发证日期", "有效期至"});
            }

        } else if (TableName.EMS_EXPERT_COMPANY.equals(tableName)) {

            List<ExpertCompany> dataList = new ArrayList<ExpertCompany>();

            if (analysisExpertCompany(list, errorList, dataList)) {
                expertCompanyServices.adminInsertSelective(dataList);
                map.put("isSuccess", true);

            } else {
                map.put("errorData", errorList);
                map.put("columnTitle", new String[]{"姓名", "身份证号", "单位名称", "单位代码", "单位地址", "单位性质", "联系方式", "所属机构", "所属机构代码"});
            }


        } else if (TableName.EMS_EXPERT_EDUCATION.equals(tableName)) {

            List<ExpertEducation> dataList = new ArrayList<ExpertEducation>();

            if (analysisExpertEducation(list, errorList, dataList)) {
                expertEducationServices.adminInsertSelective(dataList);
                map.put("isSuccess", true);

            } else {
                map.put("errorData", errorList);
                map.put("columnTitle", new String[]{"姓名", "身份证", "所学专业", "年限", "毕业院校"});

            }


        } else if (TableName.EMS_EXPERT_REVIEW.equals(tableName)) {
            List<ExpertReview> dataList = new ArrayList<ExpertReview>();

            if (analysisExpertReview(list, errorList, dataList)) {
                expertReviewServices.adminInsertSelective(dataList);
                map.put("isSuccess", true);

            } else {
                map.put("errorData", errorList);
                map.put("columnTitle", new String[]{"姓名", "身份证", "开始时间", "结束时间", "涉及企业", "涉及产品", "组长/组员"});
            }

        } else if (TableName.EMS_EXPERT_TRAIN.equals(tableName)) {
            List<ExpertTrain> dataList = new ArrayList<ExpertTrain>();

            if (analysisExpertTrain(list, errorList, dataList)) {
                expertTrainServices.adminInsertSelective(dataList);
                map.put("isSuccess", true);

            } else {
                map.put("errorData", errorList);
                map.put("columnTitle", new String[]{"姓名", "身份证号", "培训名称", "培训地址", "开始时间", "结束时间", "签到情况", "请假事由"});
            }


        } else if (TableName.EMS_EXPERT_WORK.equals(tableName)) {
            List<ExpertWork> dataList = new ArrayList<ExpertWork>();

            if (analysisExpertWork(list, errorList, dataList)) {
                expertWorkServices.adminInsertSelective(dataList);
                map.put("isSuccess", true);

            } else {
                map.put("errorData", errorList);
                map.put("columnTitle", new String[]{"姓名", "身份证", "单位名称", "从事工作内容", "开始时间", "结束时间", "职称"});
            }

        } else {
            map.put("errorMessage", "信息类型不能为空");
            return index(map);
        }
        return index(map);
    }


    private boolean analysisExpertBase(List<String[]> rows, List<String[]> errorRows, List<ExpertBase> expertBaseList) throws Exception {

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            boolean flag = true;
            //姓名
            if (StringUtils.isBlank(row[0])) {
                row[0] = "× " + row[0] + " 提示：不能为空!";
                flag = false;
            }
            //身份证号
            if (!IdCardUtil.validateIdCard(row[1] = row[1].toUpperCase())) {
                row[1] = "× " + row[1] + " 提示：格式不正确";
                flag = false;
            }
            if ((expertBaseServices.selectByIdCard(row[1])) != null) {
                row[1] = "× " + row[1] + " 提示：该专家已经存在";
                flag = false;
            }


            //性别   不为空时验证
            if (!StringUtils.isEmpty(row[2])) {
                if (!ValidatorUtil.isExistence(row[2], "男", "女")) {
                    row[2] = "× " + row[2] + " 提示：非法字符";
                    flag = false;
                }
            }

            //手机号码
            if (ValidatorUtil.isMobile(row[3]) == false) {
                row[3] = "× " + row[3] + " 提示：格式错误";
                flag = false;
            }


            //邮件  不为空时验证
            if (!StringUtils.isEmpty(row[4])) {
                if (ValidatorUtil.isEmail(row[4]) == false) {
                    row[4] = "× " + row[4] + " 提示：格式错误";
                    flag = false;
                }
            }

            //银行卡号 不验证  row[5]


            //开户行  不验证   row[6]

            //是否退休  不为空时验证
            if (!StringUtils.isEmpty(row[7])) {

                if (!ValidatorUtil.isExistence(row[7], "TRUE", "FALSE")) {
                    row[7] = "× " + row[7] + " 提示：非法字符";
                    flag = false;
                }

            }

            //是否是老师 不为空时验证
            if (!StringUtils.isEmpty(row[8])) {

                if (!ValidatorUtil.isExistence(row[8], "TRUE", "FALSE")) {
                    row[8] = "× " + row[8] + " 提示：非法字符";
                    flag = false;
                }

            }

            //是否是省外专家 不为空时验证
            if (!StringUtils.isEmpty(row[9])) {

                if (!ValidatorUtil.isExistence(row[9], "TRUE", "FALSE")) {
                    row[9] = "× " + row[9] + " 提示：非法字符";
                    flag = false;
                }

            }

            //学历 不为空时验证
            if (!StringUtils.isEmpty(row[10])) {

                if (!StaticConstant.EDUCATION_MAP.containsValue(row[10])) {
                    row[10] = "× " + row[10] + " 提示：非法字符";
                    flag = false;
                }
            }


            //职称 不为空时验证
            if (!StringUtils.isEmpty(row[11])) {
                if (!StaticConstant.RANK_MAP.containsValue(row[11])) {
                    row[11] = "× " + row[11] + " 提示：非法字符";
                    flag = false;
                }
            }

            //是否启用 
            if (!ValidatorUtil.isExistence(row[12], "TRUE", "FALSE")) {
                row[12] = "× " + row[12];
                flag = false;
            }


            if (flag == false) {
                errorRows.add(row);
            }
        }


        if (!errorRows.isEmpty()) {
            return false;
        }


        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            ExpertBase expert = new ExpertBase();
            expert.setName(row[0]);
            expert.setIdCard(row[1]);
            expert.setBirthday(IdCardUtil.getBirthByIdCard(expert.getIdCard()));
            if (!StringUtils.isEmpty(row[2])) {
                expert.setSex(row[2].equals("男") ? ShortData.s1 : ShortData.s0);
            }
            expert.setPhone(row[3]);
            expert.setEmail(row[4]);
            expert.setBankCard(row[5]);
            expert.setBankName(row[6]);

            if (!StringUtils.isEmpty(row[7])) {
                expert.setRetire(row[7].equals("TRUE") ? ShortData.s1 : ShortData.s0);
            }

            if (!StringUtils.isEmpty(row[8])) {
                expert.setTeacher(row[8].equals("TRUE") ? ShortData.s1 : ShortData.s0);
            }

            if (!StringUtils.isEmpty(row[9])) {
                expert.setOutLander(row[9].equals("TRUE") ? ShortData.s1 : ShortData.s0);
            }

            if (!StringUtils.isEmpty(row[10])) {
                expert.setEducation((Short) MapUtil.valueGetFirstKey(row[10], StaticConstant.EDUCATION_MAP));
            }

            if (!StringUtils.isEmpty(row[11])) {
                expert.setRank((Short) MapUtil.valueGetFirstKey(row[11], StaticConstant.RANK_MAP));
            }

            if (!StringUtils.isEmpty(row[12])) {
                expert.setLive(row[12].equals("TRUE") ? ShortData.s1 : ShortData.s0);
            }
            expert.setCrudType(CrudType.select);
            expertBaseList.add(expert);
        }
        return true;
    }

    private boolean analysisExpertCompany(List<String[]> rows, List<String[]> errorRows, List<ExpertCompany> expertCompanyList) throws Exception {

        Map<String, String> idList = new HashMap<String, String>();

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            boolean flag = true;

            //身份证号
            if (!IdCardUtil.validateIdCard(row[1] = row[1].toUpperCase())) {
                row[1] = "× " + row[1] + " 提示：格式不正确";
                flag = false;
            }

            ExpertBase expertBase;
            if ((expertBase = expertBaseServices.selectByIdCard(row[1])) == null) {
                row[1] = "× " + row[1] + " 提示：该专家不存在";
                flag = false;
            } else {
                if (expertCompanyServices.adminSelectByExpertIdOnly(expertBase.getId()) != null) {
                    row[1] = "× " + row[1] + " 提示：工作单位已存在";
                    flag = false;
                } else {
                    idList.put(row[1], expertBase.getId());
                }
            }

            //单位名称
            if (StringUtils.isEmpty(row[2])) {
                row[2] = "× " + row[2] + " 提示：不能为空";
                flag = false;
            }


            //单位性质  不为空时验证
            if (!StringUtils.isEmpty(row[5])) {
                if (!StaticConstant.COMPANY_PROPERTY_MAP.containsValue(row[5])) {
                    row[5] = "× " + row[5] + " 提示：只能填写”企业“、”机关“、”事业单位“、”社会团体“或”其他“";
                    flag = false;
                }
            }

            if (flag == false) {
                errorRows.add(row);
            }

        }

        if (!errorRows.isEmpty()) {
            return false;
        }

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            ExpertCompany company = new ExpertCompany();
            company.setExpertId(idList.get(row[1]));
            company.setName(row[2]);
            company.setCode(row[3]);
            company.setAddress(row[4]);
            company.setProperty((Short) MapUtil.valueGetFirstKey(row[5], StaticConstant.COMPANY_PROPERTY_MAP));
            company.setPhone(row[6]);
            company.setAffiliate(row[7]);
            company.setAffiliateCode(row[8]);
            company.setCrudType(CrudType.select);
            expertCompanyList.add(company);
        }

        return true;

    }

    private boolean analysisExpertCertificate(List<String[]> rows, List<String[]> errorRows, List<ExpertCertificate> expertCertificateList) throws Exception {

        Map<String, String> idList = new HashMap<String, String>();
        Map<String, CertificateType> idCard_CertificateType = new HashMap<String, CertificateType>();
        List<CertificateType> certificateTypeList = certificateTypeServices.selectByExample(null);


        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            boolean flag = true;

            //身份证号
            if (!IdCardUtil.validateIdCard(row[1] = row[1].toUpperCase())) {
                row[1] = "× " + row[1] + " 提示：格式不正确";
                flag = false;
            }

            ExpertBase expertBase;
            if ((expertBase = expertBaseServices.selectByIdCard(row[1])) == null) {
                row[1] = "× " + row[1] + " 提示：该专家不存在";
                flag = false;
            } else {
                idList.put(row[1], expertBase.getId());
            }


            //证书类型
            for (CertificateType ct : certificateTypeList) {
                if (ct.getName().equals(row[2])) {
                    idCard_CertificateType.put(row[1], ct);
                }
            }

            if (!idCard_CertificateType.containsKey(row[1])) {
                row[2] = "× " + row[2] + " 提示：证书类型不存在";
                flag = false;
            } else {
                if (true == expertCertificateServices.isExistCertificate(idList.get(row[1]), idCard_CertificateType.get(row[1]).getItemId())) {
                    row[2] = "× " + row[2] + " 提示：已有该事项下的证书";
                    flag = false;
                }
            }


            //证书名称
            if (StringUtils.isEmpty(row[3])) {
                row[3] = "× " + row[3] + " 提示：不能为空";
                flag = false;
            }

            //证书编号
            if (StringUtils.isEmpty(row[4])) {
                row[4] = "× " + row[4] + " 提示：不能为空";
                flag = false;
            }

            //开始时间
            Date startDate = null;
            if (StringUtils.isEmpty(row[5])) {
                row[5] = "× " + row[5] + " 提示：不能为空";
                flag = false;
            } else {
                try {
                    startDate = DateTimeUtil.parseDate(row[5], DateTimeUtil.PATTERN_DATE_ZH);
                } catch (Exception e) {
                    row[5] = "× " + row[5] + " 提示：非法字符";
                    flag = false;
                }
            }

            //结束时间
            Date endDate = null;
            if (StringUtils.isEmpty(row[6])) {
                row[6] = "× " + row[6] + " 提示：不能为空";
                flag = false;
            } else {
                try {
                    endDate = DateTimeUtil.parseDate(row[6], DateTimeUtil.PATTERN_DATE_ZH);
                } catch (Exception e) {
                    row[6] = "× " + row[6] + " 提示：非法字符";
                    flag = false;
                }
            }


            //开始时间 结束时间 的比较
            if (startDate != null && endDate != null && startDate.getTime() > endDate.getTime()) {
                row[5] = "× " + row[5] + " 提示：开始时间不能大于结束时间";
                row[6] = "× " + row[6] + " 提示：结束时间不能小于开始时间";
                flag = false;
            }

            if (flag == false) {
                errorRows.add(row);
            }

        }

        if (!errorRows.isEmpty()) {
            return false;
        }

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            ExpertCertificate expertCertificate = new ExpertCertificate();
            expertCertificate.setExpertId(idList.get(row[1]));
            expertCertificate.setCertificateTypeId(idCard_CertificateType.get(row[1]).getId());
            expertCertificate.setItemId(idCard_CertificateType.get(row[1]).getItemId());
            expertCertificate.setName(row[3]);
            expertCertificate.setNum(row[4]);
            expertCertificate.setStartTime(DateTimeUtil.parseDate(row[5], DateTimeUtil.PATTERN_DATE_ZH));
            expertCertificate.setEndTime(DateTimeUtil.parseDate(row[6], DateTimeUtil.PATTERN_DATE_ZH));
            expertCertificate.setGrade("C");
            expertCertificate.setCrudType(CrudType.select);
            expertCertificateList.add(expertCertificate);
        }

        return true;

    }

    private boolean analysisExpertEducation(List<String[]> rows, List<String[]> errorRows, List<ExpertEducation> expertEducationList) throws Exception {

        Map<String, String> idList = new HashMap<String, String>();

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            boolean flag = true;

            //身份证号
            if (!IdCardUtil.validateIdCard(row[1] = row[1].toUpperCase())) {
                row[1] = "× " + row[1] + " 提示：格式不正确";
                flag = false;
            }

            ExpertBase expertBase;
            if ((expertBase = expertBaseServices.selectByIdCard(row[1])) == null) {
                row[1] = "× " + row[1] + " 提示：该专家不存在";
                flag = false;
            } else {
                idList.put(row[1], expertBase.getId());
            }

            //所学专业
            if (StringUtils.isEmpty(row[2])) {
                row[2] = "× " + row[2] + " 提示：不能为空";
                flag = false;
            }

            //年限  不为空时验证
            if (!StringUtils.isEmpty(row[3])) {
                try {
                    double year = Double.parseDouble(row[3]);
                    if (year <= 0) {
                        throw new RuntimeException();
                    }
                } catch (Exception e) {
                    row[3] = "× " + row[3] + " 提示：非法字符";
                    flag = false;
                }
            }


            if (flag == false) {
                errorRows.add(row);
            }

        }

        if (!errorRows.isEmpty()) {
            return false;
        }

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            ExpertEducation expertEducation = new ExpertEducation();
            expertEducation.setExpertId(idList.get(row[1]));
            expertEducation.setMajor(row[2]);
            if (!StringUtils.isEmpty(row[3])) {
                expertEducation.setLearnTime(Double.parseDouble(row[3]));
            }
            expertEducation.setSchool(row[4]);
            expertEducation.setCrudType(CrudType.select);
            expertEducationList.add(expertEducation);
        }

        return true;

    }

    private boolean analysisExpertReview(List<String[]> rows, List<String[]> errorRows, List<ExpertReview> expertReviewList) throws Exception {

        Map<String, String> idList = new HashMap<String, String>();

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            boolean flag = true;

            //身份证号
            if (!IdCardUtil.validateIdCard(row[1] = row[1].toUpperCase())) {
                row[1] = "× " + row[1] + " 提示：格式不正确";
                flag = false;
            }

            ExpertBase expertBase;
            if ((expertBase = expertBaseServices.selectByIdCard(row[1])) == null) {
                row[1] = "× " + row[1] + " 提示：该专家不存在";
                flag = false;
            } else {
                idList.put(row[1], expertBase.getId());
            }

            //开始时间  不为空时验证
            Date startDate = null;
            if (!StringUtils.isEmpty(row[2])) {
                try {
                    startDate = DateTimeUtil.parseDate(row[2], DateTimeUtil.PATTERN_DATE_ZH);
                } catch (Exception e) {
                    row[2] = "× " + row[2] + " 提示：格式错误";
                    flag = false;
                }
            }
            //结束时间
            Date endDate = null;
            if (!StringUtils.isEmpty(row[3])) {
                try {
                    endDate = DateTimeUtil.parseDate(row[3], DateTimeUtil.PATTERN_DATE_ZH);
                } catch (Exception e) {
                    row[3] = "× " + row[3] + " 提示：格式错误";
                    flag = false;
                }
            }
            //开始时间 结束时间 的比较
            if (startDate != null && endDate != null && startDate.getTime() > endDate.getTime()) {
                row[2] = "× " + row[2] + " 提示：开始时间不能大于结束时间";
                row[3] = "× " + row[3] + " 提示：结束时间不能小于开始时间";
                flag = false;
            }

            //组长/组员
            if (!ValidatorUtil.isExistence(row[6], "组长", "组员")) {
                row[6] = "× " + row[6] + " 提示：填写”组长“或”组员“";
                flag = false;
            }

            if (flag == false) {
                errorRows.add(row);
            }

        }

        if (!errorRows.isEmpty()) {
            return false;
        }

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            ExpertReview expertReview = new ExpertReview();
            expertReview.setExpertId(idList.get(row[1]));
            if (!StringUtils.isBlank(row[2])) {
                expertReview.setStartTime(DateTimeUtil.parseDate(row[2], DateTimeUtil.PATTERN_DATE_ZH));
            }
            if (!StringUtils.isBlank(row[3])) {
                expertReview.setEndTime(DateTimeUtil.parseDate(row[3], DateTimeUtil.PATTERN_DATE_ZH));
            }

            expertReview.setCompany(row[4]);
            expertReview.setProduct(row[5]);
            expertReview.setRole("组长".equals(row[6]) ? ShortData.s1 : ShortData.s0);
            expertReview.setCrudType(CrudType.select);
            expertReviewList.add(expertReview);
        }

        return true;

    }

    private boolean analysisExpertTrain(List<String[]> rows, List<String[]> errorRows, List<ExpertTrain> expertTrainList) throws Exception {

        Map<String, String> idList = new HashMap<String, String>();
        Map<String, LeaveReason> idCard_leaveReason = new HashMap<String, LeaveReason>();
        List<LeaveReason> leaveReasonList = leaveReasonServices.selectByExample(null);


        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            boolean flag = true;

            //身份证号
            if (!IdCardUtil.validateIdCard(row[1] = row[1].toUpperCase())) {
                row[1] = "× " + row[1] + " 提示：格式不正确";
                flag = false;
            }

            ExpertBase expertBase;
            if ((expertBase = expertBaseServices.selectByIdCard(row[1])) == null) {
                row[1] = "× " + row[1] + " 提示：该专家不存在";
                flag = false;
            } else {
                idList.put(row[1], expertBase.getId());
            }


            //培训名称
            if (StringUtils.isEmpty(row[2])) {
                row[2] = "× " + row[2] + " 提示：不能为空";
                flag = false;
            }
            //培训地址 row[3]

            //开始时间  不为空时验证
            Date startDate = null;
            if (!StringUtils.isEmpty(row[4])) {
                try {
                    startDate = DateTimeUtil.parseDate(row[4], DateTimeUtil.PATTERN_DATE_ZH);
                } catch (Exception e) {
                    row[4] = "× " + row[4] + " 提示：格式错误";
                    flag = false;
                }
            }
            //结束时间  不为空时验证
            Date endDate = null;
            if (!StringUtils.isEmpty(row[5])) {
                try {
                    endDate = DateTimeUtil.parseDate(row[5], DateTimeUtil.PATTERN_DATE_ZH);
                } catch (Exception e) {
                    row[5] = "× " + row[5] + " 提示：格式错误";
                    flag = false;
                }
            }
            //开始时间 结束时间 的比较
            if (startDate != null && endDate != null && startDate.getTime() > endDate.getTime()) {
                row[4] = "× " + row[4] + " 提示：开始时间不能大于结束时间";
                row[5] = "× " + row[5] + " 提示：结束时间不能小于开始时间";
                flag = false;
            }

            //签到情况、请假事由

            if ("到".equals(row[6])) {
                if (!StringUtils.isEmpty(row[7])) {
                    row[7] = "× " + row[7] + " 提示：到，不能填写请假事由";
                    flag = false;
                }
            } else if ("未到".equals(row[6])) {
                for (LeaveReason leaveReason : leaveReasonList) {
                    if (leaveReason.getName().equals(row[7])) {
                        idCard_leaveReason.put(row[1], leaveReason);
                    }
                }
                if (!idCard_leaveReason.containsKey(row[1])) {
                    row[7] = "× " + row[7] + " 提示：请假事由请要与系统请假事由一致";
                    flag = false;
                }
            } else {
                row[6] = "× " + row[6] + " 提示：只能填写”到“或”未到“";
                flag = false;
            }

            if (flag == false) {
                errorRows.add(row);
            }

        }

        if (!errorRows.isEmpty()) {
            return false;
        }

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            ExpertTrain expertTrain = new ExpertTrain();
            expertTrain.setExpertId(idList.get(row[1]));
            expertTrain.setName(row[2]);
            expertTrain.setAddress(row[3]);
            if (!StringUtils.isBlank(row[4])) {
                expertTrain.setStartTime(DateTimeUtil.parseDate(row[4], DateTimeUtil.PATTERN_DATE_ZH));
            }
            if (!StringUtils.isBlank(row[5])) {
                expertTrain.setEndTime(DateTimeUtil.parseDate(row[5], DateTimeUtil.PATTERN_DATE_ZH));
            }
            expertTrain.setSignState("到".equals(row[6]) ? ShortData.s1 : ShortData.s0);
            expertTrain.setReason(row[7]);
            expertTrain.setCrudType(CrudType.select);
            expertTrainList.add(expertTrain);
        }

        return true;

    }

    private boolean analysisExpertWork(List<String[]> rows, List<String[]> errorRows, List<ExpertWork> expertWorkList) throws Exception {

        Map<String, String> idList = new HashMap<String, String>();


        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            boolean flag = true;

            //身份证号
            if (!IdCardUtil.validateIdCard(row[1] = row[1].toUpperCase())) {
                row[1] = "× " + row[1] + " 提示：格式不正确";
                flag = false;
            }

            ExpertBase expertBase;
            if ((expertBase = expertBaseServices.selectByIdCard(row[1])) == null) {
                row[1] = "× " + row[1] + " 提示：该专家不存在";
                flag = false;
            } else {
                idList.put(row[1], expertBase.getId());
            }


            //单位名称
            if (StringUtils.isEmpty(row[2])) {
                row[2] = "× " + row[2] + " 提示：不能为空";
                flag = false;
            }
            //内容 row[3]

            //开始时间  不为空时验证
            Date startDate = null;
            if (!StringUtils.isEmpty(row[4])) {
                try {
                    startDate = DateTimeUtil.parseDate(row[4], DateTimeUtil.PATTERN_DATE_ZH);
                } catch (Exception e) {
                    row[4] = "× " + row[4] + " 提示：格式错误";
                    flag = false;
                }
            }
            //结束时间  不为空时验证
            Date endDate = null;
            if (!StringUtils.isEmpty(row[5])) {
                try {
                    endDate = DateTimeUtil.parseDate(row[5], DateTimeUtil.PATTERN_DATE_ZH);
                } catch (Exception e) {
                    row[5] = "× " + row[5] + " 提示：格式错误";
                    flag = false;
                }
            }
            //开始时间 结束时间 的比较
            if (startDate != null && endDate != null && startDate.getTime() > endDate.getTime()) {
                row[4] = "× " + row[4] + " 提示：开始时间不能大于结束时间";
                row[5] = "× " + row[5] + " 提示：结束时间不能小于开始时间";
                flag = false;
            }


            //职称 不为空时验证
            if (!StringUtils.isEmpty(row[6])) {
                if (!StaticConstant.RANK_MAP.containsValue(row[6])) {
                    row[6] = "× " + row[6] + " 提示： 非法字符";
                    flag = false;
                }

            }


            if (flag == false) {
                errorRows.add(row);
            }

        }

        if (!errorRows.isEmpty()) {
            return false;
        }

        for (int i = 0; i < rows.size(); i++) {
            String[] row = rows.get(i);
            ExpertWork expertWork = new ExpertWork();
            expertWork.setExpertId(idList.get(row[1]));
            expertWork.setName(row[2]);
            expertWork.setContent(row[3]);
            if (!StringUtils.isBlank(row[4])) {
                expertWork.setStartTime(DateTimeUtil.parseDate(row[4], DateTimeUtil.PATTERN_DATE_ZH));
            }
            if (!StringUtils.isBlank(row[5])) {
                expertWork.setEndTime(DateTimeUtil.parseDate(row[5], DateTimeUtil.PATTERN_DATE_ZH));
            }
            expertWork.setRank((Short) MapUtil.valueGetFirstKey(row[6], StaticConstant.RANK_MAP));
            expertWork.setCrudType(CrudType.select);
            expertWorkList.add(expertWork);
        }

        return true;

    }


}
